let express = require('express')
let router = express.Router()
let Query = require("../tools/db")
let { getToken, verifyToken, api_url, suo } = require("../config/index")
//获取地区
router.get("/areas", async (req, res) => {
    let sql = `SELECT id,area_name FROM e_area`
    let [, result] = await Query(sql)
    res.send({
        status: 200,
        msg: "ok",
        result
    })
})
//获取年级
router.get("/grades", async (req, res) => {
    let sql = `SELECT id,grade_name FROM e_grade `
    let [, result] = await Query(sql)
    res.send({
        status: 200,
        msg: "ok",
        result
    })
})
//获取学科
router.get("/subjects", async (req, res) => {
    let sql = `SELECT id,subject_name FROM e_subject`
    let [, result] = await Query(sql)
    res.send({
        status: 200,
        msg: "ok",
        result
    })
})
//获取考试类型
router.get("/category", async (req, res) => {
    let sql = `SELECT id,category_name FROM e_category`
    let [, result] = await Query(sql)
    res.send({
        status: 200,
        msg: "ok",
        result
    })
})

//搜索
router.get("/searchlist", async (req, res, next) => {
    let { keyword, page = 1, pageSize = 6 } = req.query
    if (!keyword) {
        next("请输入关键词")
        return
    }
    let sql1 = `SELECT *  FROM e_course WHERE title LIKE '%${keyword}%' ORDER BY id DESC`
    //获取总条数
    let [, result] = await Query(sql1)
    let totalNum = result.length
    //总页码
    let pageNum = Math.ceil(totalNum / pageSize)
    //将前端传过来的page pageSize转成数字类型
    page = Number(page)
    pageSize = Number(pageSize)
    //分页  LIMIT (page-1)*pageSize,pageSize
    let sql2 = `SELECT id,cid,title,price,CONCAT("${api_url}","uploads/course/",image_src) AS image_src,type_name,area_name,subject_name FROM e_course WHERE title LIKE '%${keyword}%' ORDER BY id DESC LIMIT ${(page - 1) * pageSize},${pageSize}`
    let [, result2] = await Query(sql2)
    res.send({
        status: 200,
        msg: "ok",
        result: {
            data: result2,
            keyword,
            page,
            pageSize,
            totalNum,
            pageNum
        }
    })
})

//获取课程
router.get("/courses", async (req, res) => {
    //带有筛选功能
    let { area = "", grade = "", subject = "", category = "", page = 1, pageSize = 6 } = req.query
    let whereStr = ""
    // 地区
    if (area) {
        if (whereStr == "") {
            whereStr = `WHERE area_name='${area}'`
        } else {
            whereStr += ` AND area_name='${area}'`
        }
    }
    // 年级
    if (grade) {
        if (whereStr == "") {
            whereStr = `WHERE grade_name='${grade}'`
        } else {
            whereStr += ` AND grade_name='${grade}'`
        }
    }
    // 学科
    if (subject) {
        if (whereStr == "") {
            whereStr = `WHERE subject_name='${subject}'`
        } else {
            whereStr += ` AND subject_name='${subject}'`
        }
    }
    // 考试类别
    if (category) {
        if (whereStr == "") {
            whereStr = `WHERE category_name='${category}'`
        } else {
            whereStr += ` AND category_name='${category}'`
        }
    }
    let sql1 = `SELECT * FROM e_course ${whereStr} ORDER BY id DESC`
    let [, result1] = await Query(sql1)
    let totalNum = result1.length
    //总页码
    let pageNum = Math.ceil(totalNum / pageSize)
    //将前端传过来的page pageSize转成数字类型
    page = Number(page)
    pageSize = Number(pageSize)
    //分页  LIMIT (page-1)*pageSize,pageSize
    let sql2 = `SELECT id,cid,title,price,CONCAT("${api_url}","uploads/course/",image_src) AS image_src,type_name,area_name,subject_name FROM e_course ${whereStr} ORDER BY id DESC LIMIT ${(page - 1) * pageSize},${pageSize}`
    let [, result2] = await Query(sql2)
    res.send({
        status: 200,
        msg: "ok",
        result: {
            data: result2,
            page,
            pageSize,
            totalNum,
            pageNum
        }
    })

})

// 课程详情
router.get("/courselist", async (req, res, next) => {
    // 根据id 查询课程信息 比如：71f07c0d-f04f-4cb2-a558-7fbf2586f963
    let { id = "" } = req.query
    if (!id) {
        next("请输入必选参数")
        return
    }
    // 1.获取课程
    let sql1 = `SELECT tid,title,grade_name,subject_name,area_name,intro,price,CONCAT("${api_url}","uploads/course/",detail_imgs) AS detail_imgs,CONCAT("${api_url}","uploads/course/",image_src) AS image_src FROM e_course WHERE cid='${id}'`
    let [, result1] = await Query(sql1)
    console.log(result1);
    // 2.获取讲师
    let sql2 = `SELECT id,tid,realname,intro,CONCAT("${api_url}","uploads/avatar/",head_photo_url) AS head_photo_url FROM e_teacher WHERE tid='${result1[0].tid}'`
    let [, result2] = await Query(sql2)
    // 视频
    let sql3 = `SELECT * FROM e_video WHERE cid='${id}'`
    let [, result3] = await Query(sql3)
    // 删除数组中对象的某一属性的话  可以使用delete
    result3.forEach(item => {
        delete item.video_src
    })
    res.send({
        status: 200,
        msg: "ok",
        result: {
            course: result1[0],
            teacher: result2[0],
            video: result3
        }
    })
})
module.exports = router